package com.miniserver.tql.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Types;
import java.util.List;
import java.util.concurrent.Callable;

import org.apache.commons.lang.StringUtils;

import com.miniserver.tql.TqlMaster;
import com.miniserver.tql.TqlPool;
import com.miniserver.tql.database.ConnectionPool;
import com.miniserver.util.LogUtil;

public class HsqlTableSyncTask implements Callable<HsqlTableSyncResult> {
	private TqlMaster tqlMaster;
	private String memdbName;
	private String physicaldbName;
	private String tableName;
	private String sql;
	private long syncId;
	private String syncIdHodler;
	private String primaryKey;
	//
	private Boolean isDebug = true;
	private static LogUtil log = LogUtil.getLogUtil("HsqlTableSyncTask");
	// 保存当前物理数据库同步记录集的状态信息, 以便释放物理连接
	private Connection pConn = null;
	private PreparedStatement pState = null;
	private ResultSet pResultSet = null;

	public Boolean getIsDebug() {
		return isDebug;
	}

	private void destroy() {
		try {
			if (this.pResultSet != null) {
				this.pResultSet.close();
				this.pResultSet = null;
			}
		} catch (Exception e) {
			// ..
		}
		//
		try {
			if (this.pState != null) {
				this.pState.close();
				this.pState = null;
			}
		} catch (Exception e) {
			// ..
		}
		//
		//
		try {
			if (this.pConn != null) {
				this.pConn.close();
				this.pConn = null;
			}
		} catch (Exception e) {
			// ..
		}
	}

	@Override
	public void finalize() throws Throwable {
		this.destroy();
		//
		super.finalize();
	}

	public void setIsDebug(Boolean isDebug) {
		this.isDebug = isDebug;
	}

	public HsqlTableSyncTask(TqlMaster tqlMaster, String memdbName, String physicalDbName, String tableName,
			String sql, String primaryKey, String syncIdHolder, long syncId) {
		this.tqlMaster = tqlMaster;
		this.memdbName = memdbName;
		this.physicaldbName = physicalDbName;
		this.tableName = tableName;
		this.sql = sql;
		this.primaryKey = primaryKey;
		this.syncId = syncId;
		this.syncIdHodler = syncIdHolder;
	}

	@Override
	public HsqlTableSyncResult call() throws Exception {
		if (isDebug) {
			log.debug("开始初始化数据表" + tableName + "=====>");
		}
		HsqlTableSyncResult result = syncTable();
		return result;
	}

	private void prepareValueRow(ResultSet rs, StringBuffer values) throws Exception {
		values.append("(");
		ResultSetMetaData meta = rs.getMetaData();
		int colCount = meta.getColumnCount();
		for (int i = 1; i != colCount + 1; i++) {
			String columnName = meta.getColumnName(i);
			if (StringUtils.isBlank(rs.getString(columnName)) || "null".equals(rs.getString(columnName))) {
				values.append("null");
			} else if (meta.getColumnType(i) == Types.BIT) {
				String temp = rs.getString(i);
				if (temp.equals("f")) {
					values.append("false");
				} else if (temp.equals("t")) {
					values.append("true");
				}
			} else {
				String temp = rs.getString(i);
				if (meta.getColumnType(i) == Types.TIMESTAMP) {
					if (temp.contains(".")) {
						temp = temp.substring(0, temp.lastIndexOf("."));
					}
				}
				if (meta.getColumnType(i) == Types.VARCHAR && temp.contains("'")) {
					temp = temp.replaceAll("'", "''");
				}
				values.append("'" + temp.trim() + "'");

			}
			if (i != colCount) {
				values.append(",");
			}
		}
		values.append("),");
	}

	/**
	 * MERGE INTO TABLENAME t USING (VALUES ... ) AS vals (a,b,...,g) <br />
	 * <span> ON t.id = vals.a</span><br />
	 * WHEN MATCHED THEN <br />
	 * <span> UPDATE SET t.field1 = vals.b, t.field2 = vals.b, ... </span><br />
	 * WHEN NOT MATCHED THEN <br />
	 * <span>INSERT VALUES vals.a,vals.b,...,vals.g</span><br />
	 * 
	 * @param rs
	 * @param controler
	 * @throws Exception
	 */
	private void prepareControler(ResultSet rs, StringBuffer controler) throws Exception {
		controler.append(") AS vals(");
		// updateVals, insertVals
		ResultSetMetaData meta = rs.getMetaData();
		StringBuffer updateVals = new StringBuffer(4096);
		StringBuffer insertVals = new StringBuffer(4096);
		int colCount = meta.getColumnCount();
		for (int i = 1; i != colCount + 1; i++) {
			controler.append(getCharacter(i));
			if (i != 1) {
				updateVals.append("t.").append(meta.getColumnName(i)).append(" = ").append("vals.")
						.append(getCharacter(i));
			}
			//
			insertVals.append("vals.").append(getCharacter(i));
			controler.append(",");
			if (i != 1) {
				updateVals.append(",");
			}
			insertVals.append(",");
		}
		//
		insertVals.deleteCharAt(insertVals.length() - 1);
		updateVals.deleteCharAt(updateVals.length() - 1);
		controler.deleteCharAt(controler.length() - 1);
		controler.append(") ON t.");
		if (StringUtils.isBlank(this.primaryKey)) {
			controler.append("id");
		} else {
			controler.append(this.primaryKey);
		}
		controler.append(" = vals.a").append(" WHEN MATCHED THEN UPDATE SET ").append(updateVals);
		controler.append(" WHEN NOT MATCHED THEN INSERT VALUES ").append(insertVals).append(" ");
	}

	private boolean safeExecuteSQL(TqlPool pool, String sql) {
		boolean result = true;
		Connection conn = null;
		Statement stat = null;
		try {
			conn = pool.getConnection();
			stat = conn.createStatement();
			stat.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
			result = false;
			System.err.println("syncTable:" + this.tableName + "|" + e.getMessage());
			log.error("syncTable:" + this.tableName, e);
		}
		//
		if (stat != null) {
			try {
				stat.close();
			} catch (Exception e) {
				//
			}
			stat = null;
		}
		//
		if (conn != null) {
			try {
				conn.close();
			} catch (Exception e) {
				//
			}
			conn = null;
		}
		//
		return result;
	}

	private boolean syncRows(StringBuffer values, StringBuffer controler) {
		boolean result = true;
		if(values.length() == 0) {
			return false;
		}
		values.setLength(values.length() - 1);
		StringBuffer updateSql = new StringBuffer(4096);
		updateSql.append(" MERGE INTO ").append(tableName).append(" t USING (VALUES ").append(values).append(controler);
		String sql = updateSql.toString();
		List<TqlPool> memPools = this.tqlMaster.getMemdbPools(this.memdbName);
		for (TqlPool pool : memPools) {
			if (!this.safeExecuteSQL(pool, sql)) {
				result = false;
				break;
			}
		}
		values.setLength(0);
		return result;
	}

	private ResultSet getSyncResultSet() {
		String syncSql = null;
		try {
			ConnectionPool pool = this.tqlMaster.getPhysicalPool(this.physicaldbName);
			this.pConn = pool.getConnection();
			/*当syncId=0时，表明是初始化。
			 * 此时，全量同步
			 * 当syncId>0时，表明是同步，
			 * 此时，替换$actionid$
			 * */
			if(syncId==0){
				if(this.sql.indexOf("actionid")>0){
					syncSql=this.sql.replaceAll("actionid[ ]*[=><]+[ ]*.*", "1=1");
				}else{
					syncSql=this.sql;
				}
			}else if(syncId>0){
				if (StringUtils.isBlank(this.syncIdHodler)) {
					syncSql = this.sql;
				} else {
					syncSql = this.sql.replace(this.syncIdHodler, String.valueOf(this.syncId));
				}
			}
			this.pState = this.pConn.prepareStatement(syncSql);
			this.pResultSet = this.pState.executeQuery();
		} catch (Exception e) {
			// e.printStackTrace();
			String msg = "syncTable:" + this.tableName + "|" + syncSql;
			System.err.println(msg);
			System.err.println(e.getMessage());
			if (StringUtils.isNotBlank(syncSql)) {
				log.error(msg, e);
			} else {
				log.error("syncTable:" + this.tableName, e);
			}
		}
		return this.pResultSet;
	}

	public HsqlTableSyncResult syncTable() {
		HsqlTableSyncResult result = new HsqlTableSyncResult(tableName, true);
		StringBuffer values = new StringBuffer();
		StringBuffer controler = new StringBuffer(8192);
		ResultSet rs = this.getSyncResultSet();
		if (rs == null) {
			result.setSuccess(false);
			this.destroy();
			return result;
		}
		try {
			int count = 0;
			this.prepareControler(rs, controler);
			while (rs.next()) {
				count++;
				this.prepareValueRow(rs, values);
				if (count > 0 && count % 1000 == 0 || values.length() > 8192) {
					if (!this.syncRows(values, controler)) {
						result.setSuccess(false);
						count = 0;
						break;
					}
					count = 0;
				}
			}
			//
			if (count > 0) {
				if (!this.syncRows(values, controler)) {
					result.setSuccess(false);
				}
				count = 0;
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("syncTable:" + tableName, e);
			result.setSuccess(false);
		} finally {
			this.destroy();
		}
		//
		return result;
	}

	/**
	 * 10进制转化为字母即26进制
	 * 
	 * @param n
	 * @return
	 */
	private static String getCharacter(int n) {
		String result = "";
		while (n > 0) {
			int m = n % 26;
			if (m == 0)
				m = 26;
			result += String.valueOf(Character.toChars(97 + m - 1));
			n = (n - m) / 26;
		}
		return result;
	}
}